SUPPORT / SAMPLES & SAS NOTES
Problem Note 45496: An error occurs when you create an Excel sheet with a string greater than 255 characters using SAS/ACCESS® Interface to OLE DB
 |  |  |  |
When you use SAS/ACCESS Interface to OLE DB to write a SAS data set to Excel, if the data set contains a character variable that is longer than 255, the following error is returned:
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
libname mylib oledb init_string=" Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=c:\temp\testing.xls;Extended Properties=Excel 12.0";
data test;
length COMMENT_TEXT $550;
input COMMENT_TEXT;
datalines;
abcdefghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
abcdefghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
abcdefghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
abcdefghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
;
run;
proc datasets nolist library=mylib;
delete sheet1;run;quit;
data mylib.sheet2;
set test;
run;
ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute error: ICommand::Execute failed.
: Size of field 'COMMENT_TEXT' is too long..
Prior to SAS® 9.2, the LONGTEXT data type is used for long character columns. Beginning with SAS 9.2, SAS uses a VARCHAR data type, which has a limitation of 255 characters.
To work around this issue, use the DBTYPE= data set option as shown here:
data mylib.sheet1(dbtype=(comment_text=longtext));
set test;
run;
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to OLE DB | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows XP 64-bit Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft® Windows® for x64 | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2003 for x64 | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows Server 2008 for x64 | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Microsoft Windows XP Professional | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Windows Vista | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||
| Windows Vista for x64 | 9.21 | 9.3_M1 | 9.2 TS2M0 | 9.3 TS1M2 | ||